/**
 * Copyright 2008-2009. Chongqing Communications Industry Services Co.,Ltd
 * Information Technology Branch. All rights reserved.
 * <a>http://www.cqcis.com</a>
 */
package com.firebird.base.orm.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.SQLWarningException;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterDisposer;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;

import com.firebird.base.domain.Page;
import com.firebird.base.orm.SqlUtils;

/**
 * @author qiuxj
 * @date 2009-11-14
 */
public class BaseJdbcTemplate extends SimpleJdbcTemplate {

	protected final Log log = LogFactory.getLog(BaseJdbcTemplate.class);
	private JdbcTemplate jdbcTemplate;

	public BaseJdbcTemplate(DataSource dataSource) {
		super(dataSource);
		this.jdbcTemplate = ((JdbcTemplate) getJdbcOperations());
	}

	public BaseJdbcTemplate(JdbcOperations classicJdbcTemplate) {
		super(classicJdbcTemplate);
		this.jdbcTemplate = ((JdbcTemplate) getJdbcOperations());
	}

	public BaseJdbcTemplate(NamedParameterJdbcOperations namedParameterJdbcTemplate) {
		super(namedParameterJdbcTemplate);
		this.jdbcTemplate = ((JdbcTemplate) getJdbcOperations());
	}

	public <T> T queryForBean(String sql, Class<T> requiredType, Map args) throws DataAccessException {
		return (T) getNamedParameterJdbcOperations().queryForObject(sql, args,
				(RowMapper) ParameterizedBeanPropertyRowMapper.newInstance(requiredType));
	}

	public <T> T queryForBean(String sql, Class<T> requiredType, SqlParameterSource args) throws DataAccessException {
		return (T) getNamedParameterJdbcOperations().queryForObject(sql, args,
				(RowMapper) ParameterizedBeanPropertyRowMapper.newInstance(requiredType));
	}

	public <T> T queryForBean(String sql, Class<T> requiredType, Object[] args) throws DataAccessException {
		return (T) ((ObjectUtils.isEmpty(args)) ? getJdbcOperations().queryForObject(sql,
				(RowMapper) ParameterizedBeanPropertyRowMapper.newInstance(requiredType)) : getJdbcOperations()
				.queryForObject(sql, getArguments(args),
						(RowMapper) ParameterizedBeanPropertyRowMapper.newInstance(requiredType)));
	}

	public <T> T queryForBean(String sql, ParameterizedRowMapper<T> rm, Map args) throws DataAccessException {
		return (T) getNamedParameterJdbcOperations().queryForObject(sql, args, rm);
	}

	public <T> T queryForBean(String sql, ParameterizedRowMapper<T> rm, SqlParameterSource args)
			throws DataAccessException {
		return (T) getNamedParameterJdbcOperations().queryForObject(sql, args, rm);
	}

	public <T> T queryForBean(String sql, ParameterizedRowMapper<T> rm, Object[] args) throws DataAccessException {
		return (T) ((ObjectUtils.isEmpty(args)) ? getJdbcOperations().queryForObject(sql, rm) : getJdbcOperations()
				.queryForObject(sql, getArguments(args), rm));
	}

	public Object queryScrollAble(final String sql, ResultSetExtractor rse) throws DataAccessException {
		Assert.notNull(sql, "SQL must not be null");
		Assert.notNull(rse, "ResultSetExtractor must not be null");
		if (this.log.isDebugEnabled()) {
			this.log.debug("Executing SQL query [" + sql + "]");
		}

		return this.jdbcTemplate.execute(new ConnectionCallback() {

			private StatementCallback action;

			public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
				Statement stmt = null;
				try {
					stmt = conn.createStatement(1004, 1007);
					BaseJdbcTemplate.this.applyStatementSettings(stmt);
					Statement stmtToUse = stmt;
					if (BaseJdbcTemplate.this.jdbcTemplate.getNativeJdbcExtractor() != null) {
						stmtToUse = BaseJdbcTemplate.this.jdbcTemplate.getNativeJdbcExtractor()
								.getNativeStatement(stmt);
					}
					Object result = this.action.doInStatement(stmtToUse);
					BaseJdbcTemplate.this.handleWarnings(stmt.getWarnings());
					return result;
				} catch (SQLException ex) {
					JdbcUtils.closeStatement(stmt);

					throw BaseJdbcTemplate.this.jdbcTemplate.getExceptionTranslator().translate("StatementCallback",
							sql, ex);
				} finally {
					JdbcUtils.closeStatement(stmt);
				}
			}
		});
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize) {
		return pagedQuery(sql, pageNo, pageSize, -1);
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize, int totalCount) {
		Assert.notNull(sql, "SQL must not be null");
		if (totalCount == -1) {
			String countSqlString = "select count(*) " + SqlUtils.removeSelect(SqlUtils.removeOrders(sql));
			totalCount = queryForInt(countSqlString, new Object[0]);
		}

		if (totalCount < 1) {
			return new Page();
		}

		sql = SqlUtils.limitSelect(sql, pageNo * pageSize);
		return ((Page) queryScrollAble(sql, new PagedRowMapperResultSetExtractor(new ColumnMapRowMapper(), pageNo,
				pageSize, totalCount)));
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize, Object[] args) {
		return pagedQuery(sql, pageNo, pageSize, -1, args);
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize, int totalCount, Object[] args) {
		Assert.notNull(sql, "SQL must not be null");
		if (totalCount == -1) {
			String countSqlString = "select count(*) " + SqlUtils.removeSelect(SqlUtils.removeOrders(sql));
			totalCount = queryForInt(countSqlString, args);
		}

		if (totalCount < 1) {
			return new Page();
		}

		PagedRowMapperResultSetExtractor extractor = new PagedRowMapperResultSetExtractor(new ColumnMapRowMapper(),
				pageNo, pageSize, totalCount);

		sql = SqlUtils.limitSelect(sql, pageNo * pageSize);
		if (ObjectUtils.isEmpty(args)) {
			return ((Page) queryScrollAble(sql, extractor));
		}
		ArgPreparedStatementSetter setter = new ArgPreparedStatementSetter(getArguments(args));
		ScrollAblePreparedStatementCreator creator = new ScrollAblePreparedStatementCreator(sql);

		return ((Page) this.jdbcTemplate.query(creator, setter, extractor));
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize, int totalCount, Class<?> requiredType, Object[] args) {
		Assert.notNull(sql, "SQL must not be null");
		if (totalCount == -1) {
			String countSqlString = "select count(*) " + SqlUtils.removeSelect(SqlUtils.removeOrders(sql));
			totalCount = queryForInt(countSqlString, args);
		}

		if (totalCount < 1) {
			return new Page();
		}

		PagedRowMapperResultSetExtractor extractor = new PagedRowMapperResultSetExtractor(
				(RowMapper) ParameterizedBeanPropertyRowMapper.newInstance(requiredType), pageNo, pageSize, totalCount);

		sql = SqlUtils.limitSelect(sql, pageNo * pageSize);
		if (ObjectUtils.isEmpty(args)) {
			return ((Page) queryScrollAble(sql, extractor));
		}
		ArgPreparedStatementSetter setter = new ArgPreparedStatementSetter(getArguments(args));
		ScrollAblePreparedStatementCreator creator = new ScrollAblePreparedStatementCreator(sql);

		return ((Page) this.jdbcTemplate.query(creator, setter, extractor));
	}

	public Page pagedQuery(String sql, int pageNo, int pageSize, Class<?> requiredType, Object[] args) {
		return pagedQuery(sql, pageNo, pageSize, -1, requiredType, args);
	}

	private void applyStatementSettings(Statement stmt) throws SQLException {
		int fetchSize = this.jdbcTemplate.getFetchSize();
		if (fetchSize > 0) {
			stmt.setFetchSize(fetchSize);
		}
		int maxRows = this.jdbcTemplate.getMaxRows();
		if (maxRows > 0) {
			stmt.setMaxRows(maxRows);
		}
		DataSourceUtils.applyTimeout(stmt, this.jdbcTemplate.getDataSource(), this.jdbcTemplate.getQueryTimeout());
	}

	private void handleWarnings(SQLWarning warning) throws SQLWarningException {
		if (warning != null)
			if (this.jdbcTemplate.isIgnoreWarnings())
				if (this.log.isDebugEnabled()) {
					SQLWarning warningToLog = warning;
					while (warningToLog != null) {
						this.log.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState()
								+ "', error code '" + warningToLog.getErrorCode() + "', message ["
								+ warningToLog.getMessage() + "]");
						warningToLog = warningToLog.getNextWarning();
					}
				} else
					throw new SQLWarningException("Warning not ignored", warning);
	}

	private Object[] getArguments(Object[] varArgs) {
		if ((varArgs.length == 1) && (varArgs[0] instanceof Object[])) {
			return ((Object[]) varArgs[0]);
		}
		return varArgs;
	}

	private static class ArgPreparedStatementSetter implements PreparedStatementSetter, ParameterDisposer {
		private final Object[] args;

		public ArgPreparedStatementSetter(Object[] args) {
			this.args = args;
		}

		public void setValues(PreparedStatement ps) throws SQLException {
			if (this.args != null)
				for (int i = 0; i < this.args.length; ++i) {
					Object arg = this.args[i];
					if (arg instanceof SqlParameterValue) {
						SqlParameterValue paramValue = (SqlParameterValue) arg;
						StatementCreatorUtils.setParameterValue(ps, i + 1, paramValue, paramValue.getValue());
					} else {
						StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, arg);
					}
				}
		}

		public void cleanupParameters() {
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}

	private static class ScrollAblePreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
		private final String sql;

		public ScrollAblePreparedStatementCreator(String sql) {
			Assert.notNull(sql, "SQL must not be null");
			this.sql = sql;
		}

		public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
			return con.prepareStatement(this.sql, 1004, 1007);
		}

		public String getSql() {
			return this.sql;
		}
	}

}
